package com.cefa.finance.dao;

import com.cefa.finance.model.ContractModel;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;

import java.util.List;

@Component
@Mapper
public interface ContractMapper {

    @Select(value = "select c.id as id ,tenantry_name as tenantryName ,phone,start_date_of_lease as startDateOfLease,end_date_of_lease as endDateOfLease,travlled_kilometers as travlledKilometers," +
            "vehicle_id as vehicleId,c.vin,user_id as userId ,report_number as reportNumber,report_time as reportTime,brand from " +
            "(select a.id,tenantry_name,phone,start_date_of_lease,end_date_of_lease,vehicle_id,user_id,b.vin,b.brand,report_number,report_time,travlled_kilometers from cefa_finance_contract a  " +
            "LEFT JOIN cefa_finance_vehicle b on a.vehicle_id=b.id) c order by reportTime desc")
    List<ContractModel> findAllContracts();

    @Select(value = "select c.id as id ,tenantry_name as tenantryName ,phone,start_date_of_lease as startDateOfLease,end_date_of_lease as endDateOfLease,travlled_kilometers as travlledKilometers,  \n" +
            "vehicle_id as vehicleId,c.vin,user_id as userId ,report_number as reportNumber,report_time as reportTime,brand,accidentNum,maintenanceNum from \n" +
            "(select a.id,tenantry_name,phone,start_date_of_lease,end_date_of_lease,vehicle_id,user_id,b.vin,b.brand,report_number,report_time,travlled_kilometers,accidentNum,maintenanceNum from cefa_finance_contract a \n" +
            "LEFT JOIN cefa_finance_vehicle b on a.vehicle_id=b.id\n" +
            "LEFT JOIN (select contract_id,count(contract_id) as accidentNum from cefa_finance_accident_record GROUP BY contract_id) m on m.contract_id=a.id\n" +
            "LEFT JOIN(select contract_id,count(contract_id) as maintenanceNum  from cefa_finance_maintenance_record GROUP BY contract_id) n on n.contract_id=a.id)c order by reportTime desc\n")
    List<ContractModel> findAllVehicleContracts();

    @Select(value = "select c.id as id ,tenantry_name as tenantryName ,phone,start_date_of_lease as startDateOfLease,end_date_of_lease as endDateOfLease,travlled_kilometers as travlledKilometers,\n" +
            "vehicle_id as vehicleId,c.vin,user_id as userId ,report_number as reportNumber,report_time as reportTime,brand,repaymentNum from \n" +
            "(select a.id,tenantry_name,phone,start_date_of_lease,end_date_of_lease,vehicle_id,user_id,b.vin,b.brand,report_number,report_time,travlled_kilometers,repaymentNum from cefa_finance_contract a \n" +
            "LEFT JOIN cefa_finance_vehicle b on a.vehicle_id=b.id\n" +
            "LEFT JOIN (select contract_id,count(contract_id) as repaymentNum from cefa_finance_repayment_record GROUP BY contract_id)\n" +
            "m on m.contract_id=a.id)c order by reportTime desc")
    List<ContractModel> findAllRepaymentContracts();

    @Select(value = "select c.id as id ,tenantry_name as tenantryName ,phone,start_date_of_lease as startDateOfLease,end_date_of_lease as endDateOfLease,\n" +
            "vehicle_id as vehicleId,c.vin,user_id as userId,brand,report_time as registerDate from \n" +
            "(select a.id,tenantry_name,phone,start_date_of_lease,end_date_of_lease,vehicle_id,user_id,b.vin,b.brand,a.report_time\n" +
            "from cefa_finance_contract a LEFT JOIN cefa_finance_vehicle b on a.vehicle_id=b.id)c order by registerDate desc")
    List<ContractModel> findAllUserAccount();
}
